
import java.sql.*;
public class Conn { // 创建类Conn
    static Connection con; // 声明Connection对象
    public static String user;
    public static String password;

    public static Connection getConnection() { // 建立返回值为Connection的方法
        try { // 加载数据库驱动类
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("数据库驱动加载成功");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        user = "root";//数据库登录名
        password = "123456";//密码
        try { // 通过访问数据库的URL获取数据库连接对象

            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/shixun?useUnicode=true&characterEncoding=gbk&useSSL=false", "root", "123456");
            System.out.println("数据库连接成功");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return con; // 按方法要求返回一个Connection对象
    }

    public static void close(Connection conn) {
        try {
            if (conn != null)
                conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }



    public String select(String s1) throws SQLException {
        getConnection();
        String word = "";
        PreparedStatement psmt1 = con.prepareStatement("select * from translation where Chineseword like ? or English like ? ;");
        psmt1.setString(1, "%" + s1 + "%");
        psmt1.setString(2, "%" + s1 + "%");
        ResultSet rs = psmt1.executeQuery();
        while(rs.next()) {
                word += rs.getString(2) + "->"+rs.getString(3)+"\n";

        }
            return word;
    }
    public String add(String w_id,String Chineseword,String English) throws  SQLException {
        getConnection();
        String word = "";

        PreparedStatement psmt2 = con.prepareStatement("insert into translation values (?,?,?); ");
        psmt2.setString(1,w_id);
        psmt2.setString(2,Chineseword);
        psmt2.setString(3,English);
        psmt2.executeUpdate();

        con.close();
        return "添加成功";
    }
    public String del(String s3) throws SQLException {
        getConnection();
        PreparedStatement psmt3 = con.prepareStatement("DELETE FROM translation WHERE Chineseword LIKE ? OR English LIKE ?");
        psmt3.setString(1, "%" + s3 + "%");
        psmt3.setString(2, "%" + s3 + "%");

        int rowsAffected = psmt3.executeUpdate();
        con.close();
        if (rowsAffected > 0) {
            return "删除成功";
        } else {
            return "未找到匹配的记录";
       }
    }

    public String update(String s1, String s2, String Chineseword , String English) throws SQLException {
        getConnection();
        String sql = "UPDATE translation SET Chineseword = ?, English = ? WHERE Chineseword = ? AND English = ?";
        PreparedStatement psmt4 = con.prepareStatement(sql);
        psmt4.setString(1, s1);
        psmt4.setString(2, s2);
        psmt4.setString(3, Chineseword);
        psmt4.setString(4, English);

        int rowsAffected = psmt4.executeUpdate();
        con.close();

        if (rowsAffected > 0) {
            return "修改成功";
        } else {
            return "未找到匹配的记录";
        }
    }
        public  StringBuilder selectAll() throws SQLException {
            getConnection();
            String w_id = null;
            String Chineseword = null;
            String English = null;

            PreparedStatement psmt1 = con.prepareStatement("select * from translation;");
            ResultSet rs = psmt1.executeQuery();
            StringBuilder sb = new StringBuilder();
            while (rs.next()) {
                w_id = rs.getString("w_id");
                Chineseword = rs.getString("Chineseword");
                English = rs.getString("English");
                System.out.println(Chineseword);
                sb.append(w_id).append("  ").append(Chineseword).append(" ").append(English).append("\n");
            }
            con.close();
            return sb;
        }


    public static void main(String[] args) throws SQLException { // 主方法，测试连接
        Conn c = new Conn(); // 创建本类对象
        c.getConnection(); // 调用连接数据库的方法

    }
}


 